package com.andersen.database.dao;

import com.andersen.database.entity.ComponentTypeEntity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.HashMap;

import org.springframework.stereotype.Component;

/**
 * Created with IntelliJ IDEA.
 * User: Avseenko Dmitrij
 * Date: 05.03.14
 * Time: 12:24
 * To change this template use File | Settings | File Templates.
 */

@Component
public class ComponentTypeDao extends AbstractDao {

    // Получение типов компонентов.
    public List<ComponentTypeEntity> getComponentTypes(){

        LOG.info("ComponentTypeDao.getComponentTypes()");

        ArrayList<ComponentTypeEntity> componentTypeArrayList = new ArrayList<ComponentTypeEntity>();

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();
        
        sql.append("SELECT * FROM component_type ");
        sql.append("WHERE component_type.parent_type_id IS NULL;");
        
        try{
                        
            if (dataSource == null) LOG.error("Data Source is null!!!");
            
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            resultSet = statement.executeQuery();

            while(resultSet.next()) {
                    ComponentTypeEntity componentTypeEntity = mapResultSetRow(resultSet);
                    componentTypeArrayList.add(componentTypeEntity);
            }

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.getComponentTypes()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }

        return componentTypeArrayList;
    }

    // Получение подтипов по id родителя.
    public List<ComponentTypeEntity> getComponentSubTypesByParentId(long id){

        LOG.info("ComponentTypeDao.getComponentSubTypesByParentId()");

        ArrayList<ComponentTypeEntity> componentSubTypeArrayList = new ArrayList<ComponentTypeEntity>();

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();
        
        sql.append("SELECT * FROM component_type ");
        sql.append("WHERE component_type.parent_type_id = ?;");
        
        try{

            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setLong(1, id);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                    ComponentTypeEntity componentTypeEntity = mapResultSetRow(resultSet);
                    componentSubTypeArrayList.add(componentTypeEntity);
            }

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.getComponentSubTypesByParentId()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }

        return componentSubTypeArrayList;
    }

    // Добавление нового типа компонентов.
    public void addNewComponentType(String name){

        LOG.info("ComponentTypeDao.addNewComponentType()");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();

        sql.append("INSERT INTO equipment.component_type (name, parent_type_id) ");
        sql.append("VALUES (?, NULL);");

        try{
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, name);
            statement.executeUpdate();

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.addNewComponentType()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }

    }

    // Удаление типа\подтипа компонентов.
    public void removeComponentType(long id){

        LOG.info("ComponentTypeDao.removeComponentType()");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();

        sql.append("DELETE FROM equipment.component_type ");
        sql.append("WHERE component_type.id = ?;");

        try{
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setLong(1, id);
            statement.executeUpdate();

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.removeComponentType()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }

    }

    // Проверка на наличие наследников у типа компонентов.
    public int getNumberOfHeirsType(long id){

        Integer count = 0;

        LOG.info("ComponentTypeDao.getNumberOfHeirsType()");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();

        sql.append("SELECT count(name) AS count ");
        sql.append("FROM equipment.component_type ");
        sql.append("WHERE component_type.parent_type_id = ?;");

        try{

            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setLong(1, id);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                //ComponentTypeEntity componentTypeEntity = mapResultSetRow(resultSet);
                //componentSubTypeArrayList.add(componentTypeEntity);
                count = resultSet.getInt("count");
            }

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.getNumberOfHeirsType()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }

        return count;
    }

    // Изменение типа (название) компонентов.
    public void editComponentType(long id, String name){

        LOG.info("ComponentTypeDao.updateComponentType()");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();

        sql.append("UPDATE equipment.component_type ");
        sql.append("SET name = ? ");
        sql.append("WHERE id = ?;");

        try{
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, name);
            statement.setLong(2, id);
            statement.executeUpdate();

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.updateComponentType()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }
    }

    // Добавление нового типа компонентов.
    public void addNewComponentSubType(String name,long parentId){

        LOG.info("ComponentTypeDao.addNewComponentSubType");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();

        sql.append("INSERT INTO equipment.component_type (name, parent_type_id) ");
        sql.append("VALUES (?, ?);");

        try{
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, name);
            statement.setLong(2, parentId);
            statement.executeUpdate();

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.addNewComponentSubType", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }

    }

    // Проверка на наличие наследников у подтипа.
    public int getNumberOfHeirsSubType(long id){

        Integer count = 0;

        LOG.info("ComponentTypeDao.getNumberOfHeirsSubType()");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();

        sql.append("SELECT count(description) AS count ");
        sql.append("FROM equipment.component ");
        sql.append("WHERE component.type_id = ?;");

        try{

            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setLong(1, id);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                //ComponentTypeEntity componentTypeEntity = mapResultSetRow(resultSet);
                //componentSubTypeArrayList.add(componentTypeEntity);
                count = resultSet.getInt("count");
            }

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.getNumberOfHeirsSubType()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }

        return count;
    }

    // Изменение подтипа (название и родителя).
    public void editComponentSubType(long id, String name,long parentId){

        LOG.info("ComponentTypeDao.updateComponentSubType()");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();

        sql.append("UPDATE equipment.component_type ");
        sql.append("SET name = ?, parent_type_id = ? ");
        sql.append("WHERE id = ?;");

        try{
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, name);
            statement.setLong(2, parentId);
            statement.setLong(3, id);

            statement.executeUpdate();

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.updateComponentSubType()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }


    }

    // Возвращаем тип компонентов по id.
    public ComponentTypeEntity getTypeById(long id) {
        ComponentTypeEntity ent = null;
        Connection connection = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;

        String request = "SELECT * FROM component_type ct WHERE ct.id = ?;";

        try{
            connection = dataSource.getConnection();
            statment = connection.prepareStatement(request);
            statment.setLong(1, id);
            resultSet = statment.executeQuery();
            if(resultSet.first()) {
                ent = mapResultSetRow(resultSet);
            }
        }catch(SQLException exc) {
            LOG.error("Error getTypeById", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statment);
            safeClose(connection);
        }

        return ent;
    }

    public HashMap<Long,ArrayList<ComponentTypeEntity>> getAllComponentsTypes() {
        LOG.info("ComponentTypeDao.getAllComponentsTypes()");

        HashMap<Long,ArrayList<ComponentTypeEntity>> map = new HashMap<Long,ArrayList<ComponentTypeEntity>>();

        Connection connection = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;

        String request = "SELECT * FROM component_type ORDER BY component_type.name;";

        try{

            connection = dataSource.getConnection();
            statment = connection.prepareStatement(request);
            resultSet = statment.executeQuery();
            while(resultSet.next()) {
                ComponentTypeEntity componentTypeEntity = mapResultSetRow(resultSet);
                Long key = componentTypeEntity.getParentTypeId();
                if (!map.containsKey(key)) map.put(key, new ArrayList<ComponentTypeEntity>());
                map.get(key).add(componentTypeEntity);
            }

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.getAllComponentsTypes()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statment);
            safeClose(connection);
        }

        return map;
    }

    private ComponentTypeEntity mapResultSetRow(ResultSet rs) throws SQLException {
        ComponentTypeEntity ct = new ComponentTypeEntity();
        ct.setId(rs.getLong("id"));
        ct.setName(rs.getString("name"));
        ct.setParentTypeId(rs.getLong("parent_type_id"));
        return ct;
    }
}
